package org.cx.util;

import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import javassist.bytecode.ByteArray;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.cx.entity.PageData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.multipart.MultipartFile;

public class ExcelUtils {

    protected static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    // 上传公共路径
    public static final String UPLOAD_PATH = "chainJKT/upload_files";
    // 图片检查失败时，返回错误信息的key值
    public static final String ERROR_KEY = "error";
    // 成功上传图片后，返回的相对路径地址
    public static final String IMAGE_URL = "imgUrl";
    public static final String IMAGE_SIZE = "imgSize";

    //上传文件成功返回相对路径
    public static final String FILE_URL = "file";
    public static final String FILESIZE = "fileSize";

    public static final String NO_FILE = "上传文件不能为空";
    public static final String UNKNOWN = "未知错误";

    public static final String FILE_NAME = "fileName";

    private static final String DATE_PATTERN = "yyyyMMdd";
    private static final String TIME_PATTERN = "yyyyMMddHHmmss";

    public static SimpleDateFormat sdf = new SimpleDateFormat(DATE_PATTERN);
    public static SimpleDateFormat df = new SimpleDateFormat(TIME_PATTERN);

    // 定义允许上传的文件扩展名
    public static HashMap<String, String> extMap = new HashMap<String, String>();

    static {
        extMap.put("image", "gif,jpg,png");
        extMap.put("file", "xlsx,xls");

    }

    // 上传的最大不超过2M，单位是以bytes
    public static long maxSize = 2 * 1024 * 1024;
    public static long maxSizeFile = 10 * 1024 * 1024;




    /**
     * 传入查询对象，
     * */
    public static void fillExcelForFile(List<PageData> list, String[] titles, String[] onlyFilds,String fileName,HttpServletResponse response) {
        OutputStream out = null;
        try {
            HSSFWorkbook workbook = fillExcel(list, titles, onlyFilds);
            out = response.getOutputStream();
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
            String tablename = dateFormat.format(new Date());
            fileName = fileName + tablename + ".xls";
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(out);
            response.getOutputStream().flush();
            response.getOutputStream().close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }




    /**
     * 填充数据到execl
     *
     * @param list      基本数据
     * @param titles    表头列名称
     * @param onlyFilds 导出的对象属性名称
     */
    public static HSSFWorkbook fillExcel(List<PageData> list, String[] titles, String[] onlyFilds) throws Exception {
        // 创建并且设置格式
        HSSFWorkbook wb = new HSSFWorkbook();
        //03版本最大行数65536
        int sheetCount = (null == list || 0 == list.size() ? 0 : list.size() / 65535) + 1;
        for (int i = 0; i < sheetCount; i++) {
            HSSFSheet sheet = wb.createSheet();
            HSSFCellStyle titleCellStyle = titleCellStyle(wb);
            HSSFCellStyle cellStyle = cellStyle(wb);
            setHeaderTitle(sheet, titles, titleCellStyle);
            if (list != null) {
                for (int r = 0, y = i * 65535; r < 65535 && y < list.size(); r++, y++) {
                	PageData pd = list.get(y);
                    HSSFRow row = sheet.createRow(r + 1);
                    setRowCellValue(cellStyle, row, pd, onlyFilds);
                }
            }
        }
        return wb;

    }

    /**
     * 将一个对象的指定属性值填充到ROW里面
     *
     * @param row       HSSFROw对象
     * @param onlyFilds 的字段
     */

    public static void setRowCellValue(HSSFCellStyle cellStyle, HSSFRow row, PageData pd, String[] onlyFilds) throws Exception {
        for (int i = 0; i < onlyFilds.length; i++) {
        	Object val = pd.get(onlyFilds[i]);
                HSSFCell cell = row.createCell(i);
                if ((val instanceof Date) && val != null) {
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cell.setCellValue(val == null ? null : formatter.format(val));
                } else {
                    cell.setCellValue(val == null ? null : val.toString());
                }
                cell.setCellStyle(cellStyle);
            }
    }


    /**
     * 创建单元格类型
     *
     * @param wb
     * @return
     */
    public static HSSFCellStyle titleCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 指定当单元格内容显示不下时自动换行
        titleCellStyle.setWrapText(true);
        // 设置单元格字体
        HSSFFont titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 12);
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleCellStyle.setFont(titleFont);

        return titleCellStyle;
    }


    /**
     * 初始化表格单元格样式，可重写该方法实现自定义单元格样式
     */
    public static HSSFCellStyle cellStyle(HSSFWorkbook wb) {
        // 创建单元格样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐，边框为细
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(font);
        return cellStyle;
    }


    /**
     * 设置表头
     *
     * @return
     */
    public static void setHeaderTitle(HSSFSheet sheet, String[] titles, HSSFCellStyle cellStyle) {
        HSSFRow headerRow = sheet.createRow(0);
        HSSFCell headerCell = null;
        for (int c = 0; c < titles.length; c++) {
            headerCell = headerRow.createCell(c);
            headerCell.setCellStyle(cellStyle);
            headerCell.setCellValue(titles[c]);
            sheet.setColumnWidth(c, (30 * 160));
        }
    }

    /**
     * 返回表头字体类型
     *
     * @param titleFont
     * @return
     */
    public static HSSFFont getHeaderFont(HSSFFont titleFont) {
        titleFont.setFontHeightInPoints((short) 12);
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        return titleFont;
    }

    public static void responseWrite(HSSFWorkbook book, HttpServletRequest request, HttpServletResponse response) {
        try {
            // 时间
            Date now = new Date();
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");// 可以方便地修改日期格式
            String codedFileName = "门店列表" + dateFormat.format(now);//java.net.URLEncoder.encode(sqlAndparameter.get("sqlHead") + dateFormat.format(now),"UTF-8");
            String userAgent = request.getHeader("User-Agent");
            //针对IE或者以IE为内核的浏览器：
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                codedFileName = java.net.URLEncoder.encode(codedFileName, "UTF-8");
            } else {//非IE浏览器的处理：
                codedFileName = new String(codedFileName.getBytes("UTF-8"), "iso-8859-1");
            }
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");

            OutputStream outputStream = response.getOutputStream();
            book.write(outputStream);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void mergedCell(HSSFSheet sheet, int startRow, int endRow, int startCol, int endCol) {
        CellRangeAddress region = new CellRangeAddress(startRow, endRow, startCol, endCol);
        sheet.addMergedRegion(region);
    }


    public static Map<String, Object> doUploadFile(MultipartFile file, String savePath, String dirName) throws Exception {
        if (file != null) {
            Map<String, Object> ret = new HashMap<String, Object>();

            String filePathName = file.getOriginalFilename();
            // 文件保存目录URL
            String photoUrl = "/"+UPLOAD_PATH+"/";

            File uploadDir = new File(savePath);
            if (!uploadDir.isDirectory()) {
                uploadDir.mkdir();
            }
            if (!uploadDir.canWrite()) {
                ret.put(ERROR_KEY, "上传文件没有写权限！");
                return ret;
            }
            if (dirName == null) {
                dirName = "file";
            }

            // 创建文件夹

            if (StringUtils.isNotBlank(savePath) && !savePath.endsWith("/")) {
                savePath += "/";
            }
            savePath += dirName + "/";
            photoUrl += dirName + "/";
            File saveDirFile = new File(savePath);
            if (!saveDirFile.exists()) {
                saveDirFile.mkdirs();
            }

            String ymd = sdf.format(new Date());
            savePath += ymd + "/";
            photoUrl += ymd + "/";
            File dirFile = new File(savePath);
            if (!dirFile.exists()) {
                dirFile.mkdirs();
            }

            // 检查文件大小
            long fileSize = file.getSize();
            if (fileSize > maxSizeFile) {
                ret.put(ERROR_KEY, "上传文件大小超过限制。最大值10M。");
                return ret;
            }

            // 检查扩展名
            String fileExt = filePathName.substring(
                    filePathName.lastIndexOf(".") + 1).toLowerCase();

            if (!Arrays.<String>asList(extMap.get("file").split(",")).contains(fileExt)) {
                ret.put(ERROR_KEY, "上传文件扩展名是不允许的扩展名。\n只允许" + extMap.get("file") + "格式。");
                return ret;
            }

            String newFileName = df.format(new Date()) + "_" + new Random().nextInt(1000) + "." + fileExt;
            photoUrl += newFileName;

            File dest = new File(savePath, newFileName);
            file.transferTo(dest);



            ret.put(FILE_NAME, filePathName);
            ret.put(FILESIZE, fileSize);
            ret.put(FILE_URL, photoUrl);
            return ret;
        }
        return null;
    }

    /**
     * 将文件用流的方式输出
     * @param file
     * @param response
     * @throws Exception
     */

    public static void outPutFile(File file,HttpServletResponse response)throws Exception{
        InputStream in=null;
        OutputStream out=null;
        try {
            response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(file.getName(),"UTF-8"));
            in=new FileInputStream(file);
            int len=0;
            byte buffer[]=new byte[1024];
            out=response.getOutputStream();
            while((len=in.read(buffer))!=-1){
                out.write(buffer,0,len);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(in!=null){
                in.close();
            }
        }
    }





}
